package com.huabo.contract.mysql.mapper;

import com.hbfk.util.DateUtil;
import com.hbfk.util.PageInfo;
import com.huabo.contract.mysql.entity.TblContractPaymentMySql;
import com.huabo.contract.mysql.entity.TblContractPlannodeMySql;

import java.math.BigDecimal;

public class TblContractPlannodeMySqlMapperSqlConfig {

    public String findPlanNodeListForCollection(PageInfo<TblContractPlannodeMySql> pageInfo, TblContractPlannodeMySql node) {
    	TblContractPaymentMySql tcp = new TblContractPaymentMySql();
        StringBuffer sbSql = new StringBuffer("SELECT * FROM ( SELECT BUDGET.*,ROWNUM RNUM FROM (SELECT TCP.NODEID,TCP.NODECONTENT,TCP.PLANSTARTDATE,TCP.PLANENDDATE,TCP.NODEPOST*TCU.CONTRACTMONEY/100 AS YFMONEY,TCS.NODECONTENT AS SPNODECONTENT,TCP.NODEPOST \n" +
                "FROM TBL_CONTRACT_PLANNODE TCP LEFT JOIN TBL_CYHW_UNIT TCU ON TCP.PROJECTID = TCU.CONTRACTID LEFT JOIN TBL_CONTRACT_SPNODE TCS ON TCP.NODEID = TCS.NODEID WHERE TCP.PROJECTID =  " + node.getProjectid() + " AND TCP.PLANNODESTATUS = 2 AND TCP.NODEID NOT IN (SELECT NODEID FROM TBL_CONTRACT_COLLECTION WHERE COLLECTIONSTATUS != 4)");
        if (tcp.getNodecontent() != null && !"".equals(tcp.getNodecontent())) {
            sbSql.append(" AND TCP.NODECONTENT LIKE '%" +tcp.getNodecontent() + "%'");
        }

        sbSql.append(" ORDER BY TCP.PLANSTARTDATE ASC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RNUM > "+pageInfo.getCurrentRecord());
        String sql = sbSql.toString();
        return sql;
    }

    public String selectCountByPageInfo(TblContractPlannodeMySql node) {
    	TblContractPaymentMySql tcp = new TblContractPaymentMySql();
        StringBuffer sbSql = new StringBuffer("SELECT count(*) FROM TBL_CONTRACT_PLANNODE TCP LEFT JOIN TBL_CYHW_UNIT TCU ON TCP.PROJECTID = TCU.CONTRACTID LEFT JOIN TBL_CONTRACT_SPNODE TCS ON TCP.NODEID = TCS.NODEID WHERE TCP.PROJECTID =  " + node.getProjectid() + " AND TCP.PLANNODESTATUS = 2 AND TCP.NODEID NOT IN (SELECT NODEID FROM TBL_CONTRACT_COLLECTION WHERE COLLECTIONSTATUS != 4)");
        if (tcp.getNodecontent() != null && !"".equals(tcp.getNodecontent())) {
            sbSql.append(" AND TCP.NODECONTENT LIKE '%" +tcp.getNodecontent() + "%'");
        }

       // sbSql.append(" ORDER BY TCU.CONTRACTID DESC)");
        String sql = sbSql.toString();
        return sql;
    }


    public String findPlanNodeListForPayment(PageInfo<TblContractPlannodeMySql> pageInfo, TblContractPlannodeMySql node) {
        StringBuffer sbSql = new StringBuffer("SELECT * FROM ( SELECT BUDGET.*,ROWNUM RNUM FROM (SELECT TCP.NODEID,TCP.NODECONTENT,TCP.PLANSTARTDATE,TCP.PLANENDDATE,TCP.NODEPOST*TCU.CONTRACTMONEY/100 AS YFMONEY,TCS.NODECONTENT AS SPNODECONTENT,TCP.NODEPOST FROM TBL_CONTRACT_PLANNODE TCP LEFT JOIN TBL_CYHW_UNIT TCU ON TCP.PROJECTID = TCU.CONTRACTID LEFT JOIN TBL_CONTRACT_SPNODE TCS ON TCP.NODEID = TCS.NODEID WHERE TCP.PROJECTID = " + node.getProjectid() + " AND TCP.PLANNODESTATUS = 2 AND TCP.NODEID NOT IN (SELECT NODEID FROM TBL_CONTRACT_PAYMENT WHERE PAYMENTSTATUS != 4)");
        if (node.getNodecontent() != null && !"".equals(node.getNodecontent())) {
            sbSql.append(" AND TCP.NODECONTENT LIKE '%" + node.getNodecontent() + "%'");
        }

        sbSql.append(" ORDER BY TCP.PLANSTARTDATE ASC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RNUM > "+pageInfo.getCurrentRecord());
        String sql = sbSql.toString();
        return sql;
    }


    public String findPlanNodeListForPaymentCount(TblContractPlannodeMySql node) {
        StringBuffer sbSql = new StringBuffer("SELECT COUNT(*) FROM TBL_CONTRACT_PLANNODE TCP LEFT JOIN TBL_CYHW_UNIT TCU ON TCP.PROJECTID = TCU.CONTRACTID LEFT JOIN TBL_CONTRACT_SPNODE TCS ON TCP.NODEID = TCS.NODEID WHERE TCP.PROJECTID = " + node.getProjectid() + " AND TCP.PLANNODESTATUS = 2 AND TCP.NODEID NOT IN (SELECT NODEID FROM TBL_CONTRACT_PAYMENT WHERE PAYMENTSTATUS != 4)");
        if (node.getNodecontent() != null && !"".equals(node.getNodecontent())) {
            sbSql.append(" AND TCP.NODECONTENT LIKE '%" + node.getNodecontent() + "%'");
        }

        //sbSql.append(" ORDER BY TCP.PLANSTARTDATE ASC) ");
        String sql = sbSql.toString();
        return sql;
    }

    public String findeSumMoneyByContractId(Integer contractId, BigDecimal nodeid) throws Exception {
        String sql = "SELECT NVL(SUM(NODEMONEY) ,0) FROM TBL_CONTRACT_PLANNODE WHERE PROJECTID = "+contractId;
        if(nodeid != null) {
            sql += " AND NODEID != "+nodeid;
        }
        return sql;
    }

    public String saveContractPlannode(TblContractPlannodeMySql node) {
        StringBuffer column = new StringBuffer("INSERT INTO TBL_CONTRACT_PLANNODE (NODEID");
        StringBuffer value = new StringBuffer(" VALUES (HIBERNATE_SEQUENCE.nextval");

        if(node.getNodecontent() != null && !"".equals(node.getNodecontent())) {
            column.append(",NODECONTENT");
            value.append(",'"+node.getNodecontent()+"'");
        }
        if(node.getProjectid() != null && !"".equals(node.getProjectid())) {
            column.append(",PROJECTID");
            value.append(",'"+node.getProjectid()+"'");
        }
        if(node.getBlprojectid() != null && !"".equals(node.getBlprojectid())) {
            column.append(",BLPROJECTID");
            value.append(",'"+node.getBlprojectid()+"'");
        }
        if(node.getNodepost() != null && !"".equals(node.getNodepost())) {
            column.append(",NODEPOST");
            value.append(",'"+node.getNodepost()+"'");
        }
        if(node.getDispatchstaff() != null && !"".equals(node.getDispatchstaff())) {
            column.append(",DISPATCHSTAFF");
            value.append(",'"+node.getDispatchstaff()+"'");
        }
        if(node.getDispatchdept() != null && !"".equals(node.getDispatchdept())) {
            column.append(",DISPATCHDEPT");
            value.append(",'"+node.getDispatchdept()+"'");
        }
        if(node.getPlannodestatus() != null && !"".equals(node.getPlannodestatus())) {
            column.append(",PLANNODESTATUS");
            value.append(",'"+node.getPlannodestatus()+"'");
        }
        if(node.getContractid() != null && !"".equals(node.getContractid())) {
            column.append(",CONTRACTID");
            value.append(",'"+node.getContractid()+"'");
        }
        if(node.getFeedback() != null && !"".equals(node.getFeedback())) {
            column.append(",FEEDBACK");
            value.append(",'"+node.getFeedback()+"'");
        }
        if(node.getNodemoney() != null && !"".equals(node.getNodemoney())) {
            column.append(",NODEMONEY");
            value.append(",'"+node.getNodemoney()+"'");
        }
        if(node.getPlanstartdate() != null) {
            column.append(",PLANSTARTDATE");
            value.append(",TO_DATE('"+ DateUtil.parseDate(node.getPlanstartdate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(node.getPlanenddate() != null) {
            column.append(",PLANENDDATE");
            value.append(",TO_DATE('"+DateUtil.parseDate(node.getPlanenddate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(node.getNodeplanpaydate() != null) {
            column.append(",NODEPLANPAYDATE");
            value.append(",TO_DATE('"+DateUtil.parseDate(node.getNodeplanpaydate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        column.append(")");
        value.append(")");
        String sql = column.toString()+value.toString();
        return sql;
    }

    public String updateContractPlannode(TblContractPlannodeMySql node) {
        StringBuffer sql = new StringBuffer("UPDATE TBL_CONTRACT_PLANNODE SET NODECONTENT = '"+node.getNodecontent()+"'");

        if(node.getProjectid() != null) {
            sql.append(" ,PROJECTID = '"+node.getProjectid()+"'");
        }
        if(node.getBlprojectid() != null) {
            sql.append(" ,BLPROJECTID = '"+node.getBlprojectid()+"'");
        }
        if(node.getNodepost() != null) {
            sql.append(" ,NODEPOST = '"+node.getNodepost()+"'");
        }
        if(node.getDispatchstaff() != null) {
            sql.append(" ,DISPATCHSTAFF = '"+node.getDispatchstaff()+"'");
        }
        if(node.getDispatchdept() != null) {
            sql.append(" ,DISPATCHDEPT = '"+node.getDispatchdept()+"'");
        }
        if(node.getPlannodestatus() != null) {
            sql.append(" ,PLANNODESTATUS = '"+node.getPlannodestatus()+"'");
        }
        if(node.getContractid() != null) {
            sql.append(" ,CONTRACTID = '"+node.getContractid()+"'");
        }
        if(node.getFeedback() != null) {
            sql.append(" ,FEEDBACK = '"+node.getFeedback()+"'");
        }
        if(node.getNodemoney() != null) {
            sql.append(" ,NODEMONEY = '"+node.getNodemoney()+"'");
        }
        if(node.getPlanstartdate() != null) {
            sql.append(" ,PLANSTARTDATE = TO_DATE('"+DateUtil.parseDate(node.getPlanstartdate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(node.getPlanenddate() != null) {
            sql.append(" ,PLANENDDATE = TO_DATE('"+DateUtil.parseDate(node.getPlanenddate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(node.getNodeplanpaydate() != null) {
            sql.append(" ,NODEPLANPAYDATE = TO_DATE('"+DateUtil.parseDate(node.getNodeplanpaydate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }

        sql.append(" WHERE NODEID = '"+node.getNodeid()+"'");
        return sql.toString();
    }

    public String findContractNodeListByPageInfo(PageInfo<TblContractPlannodeMySql> pageInfo) throws Exception {
    	TblContractPlannodeMySql node = pageInfo.getCondition();
        StringBuffer sbSql = new StringBuffer("SELECT * FROM ( SELECT BUDGET.*,ROWNUM RNUM FROM (SELECT NODEID,NODECONTENT,PROJECTID,PLANSTARTDATE,PLANENDDATE,NODEPOST,NODEPLANPAYDATE,DISPATCHSTAFF,DISPATCHDEPT,PLANNODESTATUS,feedback FROM TBL_CONTRACT_PLANNODE WHERE PROJECTID = " + node.getProjectid() );
        if (node.getDispatchstaff() != null && !"".equals(node.getDispatchstaff())) {
            sbSql.append(" AND DISPATCHSTAFF = " + node.getDispatchstaff());
        }

        if (node.getNodecontent() != null && !"".equals(node.getNodecontent())) {
            sbSql.append(" AND NODECONTENT LIKE '%" + node.getNodecontent() + "%'");
        }
        sbSql.append(" ORDER BY NODEID ASC,PLANSTARTDATE ASC,PLANENDDATE ASC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RNUM > "+pageInfo.getCurrentRecord());
        String sql = sbSql.toString();
        return sql;
    }
    public String findCountContractNodeByPageInfo(PageInfo<TblContractPlannodeMySql> pageInfo) throws Exception {
    	TblContractPlannodeMySql node = pageInfo.getCondition();
        StringBuffer sbSql = new StringBuffer("SELECT COUNT(*) FROM TBL_CONTRACT_PLANNODE WHERE PROJECTID = " + node.getProjectid() );
        if (node.getDispatchstaff() != null && !"".equals(node.getDispatchstaff())) {
            sbSql.append(" AND DISPATCHSTAFF = " + node.getDispatchstaff());
        }

        if (node.getNodecontent() != null && !"".equals(node.getNodecontent())) {
            sbSql.append(" AND NODECONTENT LIKE '%" + node.getNodecontent() + "%'");
        }
        return sbSql.toString();
    }


}
